package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bfc.BFCMessage;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.wms.MESMaterialReturnItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.CoreServiceImpl;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class MESMaterialReturnItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(MESMaterialReturnItemDAO.class);

    private static MESMaterialReturnItemDAO Instance = null;

    private MESMaterialReturnItemDAO() {
        super();
    }

    public static MESMaterialReturnItemDAO getInstance() {
        if (Instance == null)
            Instance = new MESMaterialReturnItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, MESMaterialReturnItem wMESMaterialReturnItem,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wMESMaterialReturnItem == null)
                return 0;

            String wSQL = "";
            if (wMESMaterialReturnItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.mes_materialreturnitem(ReturnID,MaterialID,MaterialNo,"
                                + "MaterialName,RFQTY,ProductTime,Stock,Supplier,WBSNo,BatchNo,SerialNo,AssessmentType,"
                                + "PartPointID,RowNo,ReplaceType,OutSourceType,BackReason,BOMNumber,PLNumber," +
                                "DemandID,ReturnListID,DemandItemID,InspectorID,BadNumber,BadReason,InspectTime," +
                                "ProductName," +
                                "ExpirationDate,WarehousingDate,SupplierLotNumber,ItemStockID) VALUES(:ReturnID," +
                                ":MaterialID," +
                                ":MaterialNo,"
                                +
                                ":MaterialName,"
                                + ":RFQTY,:ProductTime,:Stock,:Supplier,:WBSNo,:BatchNo,:SerialNo,:AssessmentType,"
                                + ":PartPointID,:RowNo,:ReplaceType,:OutSourceType,:BackReason,:BOMNumber,:PLNumber," +
                                ":DemandID,:ReturnListID,:DemandItemID,:InspectorID,:BadNumber,:BadReason," +
                                ":InspectTime," +
                                ":ProductName,:ExpirationDate,:WarehousingDate,:SupplierLotNumber,:ItemStockID);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.mes_materialreturnitem SET ReturnID = :ReturnID,MaterialID = :MaterialID,"
                                + "MaterialNo = :MaterialNo,MaterialName = :MaterialName,RFQTY = :RFQTY,"
                                + "ProductTime = :ProductTime,Stock = :Stock,Supplier = :Supplier,WBSNo = :WBSNo,"
                                + "BatchNo = :BatchNo,SerialNo = :SerialNo,AssessmentType = :AssessmentType,"
                                + "PartPointID = :PartPointID,RowNo = :RowNo,"
                                + "ReplaceType=:ReplaceType,OutSourceType=:OutSourceType,"
                                + "BackReason=:BackReason,BOMNumber=:BOMNumber,PLNumber=:PLNumber,DemandID=:DemandID," +
                                "ReturnListID=:ReturnListID,DemandItemID=:DemandItemID,InspectorID=:InspectorID," +
                                "BadNumber="
                                +
                                ":BadNumber,BadReason=:BadReason,InspectTime=:InspectTime,ProductName=:ProductName," +
                                "ExpirationDate=:ExpirationDate,WarehousingDate=:WarehousingDate," +
                                "SupplierLotNumber=:SupplierLotNumber,ItemStockID=:ItemStockID " +
                                "WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wMESMaterialReturnItem.ID);
            wParamMap.put("ReturnID", wMESMaterialReturnItem.ReturnID);
            wParamMap.put("MaterialID", wMESMaterialReturnItem.MaterialID);
            wParamMap.put("MaterialNo", wMESMaterialReturnItem.MaterialNo);
            wParamMap.put("MaterialName", wMESMaterialReturnItem.MaterialName);
            wParamMap.put("RFQTY", wMESMaterialReturnItem.RFQTY);
            wParamMap.put("ProductTime", wMESMaterialReturnItem.ProductTime);
            wParamMap.put("Stock", wMESMaterialReturnItem.Stock);
            wParamMap.put("Supplier", wMESMaterialReturnItem.Supplier);
            wParamMap.put("WBSNo", wMESMaterialReturnItem.WBSNo);
            wParamMap.put("BatchNo", wMESMaterialReturnItem.BatchNo);
            wParamMap.put("SerialNo", wMESMaterialReturnItem.SerialNo);
            wParamMap.put("AssessmentType", wMESMaterialReturnItem.AssessmentType);
            wParamMap.put("PartPointID", wMESMaterialReturnItem.PartPointID);
            wParamMap.put("RowNo", wMESMaterialReturnItem.RowNo);
            wParamMap.put("ReplaceType", wMESMaterialReturnItem.ReplaceType);
            wParamMap.put("OutSourceType", wMESMaterialReturnItem.OutSourceType);
            wParamMap.put("BackReason", wMESMaterialReturnItem.BackReason);
            wParamMap.put("BOMNumber", wMESMaterialReturnItem.BOMNumber);
            wParamMap.put("PLNumber", wMESMaterialReturnItem.PLNumber);
            wParamMap.put("DemandID", wMESMaterialReturnItem.DemandID);
            wParamMap.put("ReturnListID", wMESMaterialReturnItem.ReturnListID);
            wParamMap.put("DemandItemID", wMESMaterialReturnItem.DemandItemID);
            wParamMap.put("InspectorID", wMESMaterialReturnItem.InspectorID);
            wParamMap.put("BadNumber", wMESMaterialReturnItem.BadNumber);
            wParamMap.put("BadReason", wMESMaterialReturnItem.BadReason);
            wParamMap.put("InspectTime", wMESMaterialReturnItem.InspectTime);
            wParamMap.put("ProductName", wMESMaterialReturnItem.ProductName);
            wParamMap.put("ExpirationDate", wMESMaterialReturnItem.ExpirationDate);
            wParamMap.put("WarehousingDate", wMESMaterialReturnItem.WarehousingDate);
            wParamMap.put("SupplierLotNumber", wMESMaterialReturnItem.SupplierLotNumber);
            wParamMap.put("ItemStockID", wMESMaterialReturnItem.ItemStockID);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wMESMaterialReturnItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wMESMaterialReturnItem.setID(wResult);
            } else {
                wResult = wMESMaterialReturnItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<MESMaterialReturnItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (MESMaterialReturnItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.mes_materialreturnitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public MESMaterialReturnItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        MESMaterialReturnItem wResult = new MESMaterialReturnItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<MESMaterialReturnItem> wList = SelectList(wLoginUser, wID, -1, -1, -1, "", wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<MESMaterialReturnItem> SelectList(BMSEmployee wLoginUser, int wID, int wReturnID, int wMaterialID,
                                                  int wPartPointID, String wMaterial, OutResult<Integer> wErrorCode) {
        List<MESMaterialReturnItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.CYUnitID,t3.DemandNo,t4.Name Inspector,t1.Groes,t1.Normt,t2.BOMItemNum " +
                            "APSBOMItemNum FROM {0}" +
                            ".mes_materialreturnitem t"
                            + " left join {0}.mss_material t1 on t.MaterialID=t1.ID "
                            + " left join {0}.wms_pickdemanditem t2 on t.DemandItemID=t2.ID "
                            + " left join {0}.wms_pickdemand t3 on t2.DemandID=t3.ID "
                            + " left join {0}.mbs_user t4 on t.InspectorID=t4.ID "
                            + "WHERE  1=1  "
                            + "and ( :wID <= 0 or :wID = t.ID ) "
                            + "and ( :wReturnID <= 0 or :wReturnID = t.ReturnID ) "
                            + "and ( :wMaterialID <= 0 or :wMaterialID = t.MaterialID ) "
                            + "and ( :wMaterial is null or :wMaterial = '''' or (t.MaterialName like ''%{1}%'' or t" +
                            ".MaterialNo like ''%{1}%'') ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID );",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wReturnID", wReturnID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            for (Map<String, Object> wReader : wQueryResult) {
                MESMaterialReturnItem wItem = new MESMaterialReturnItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.ReturnID = StringUtils.parseInt(wReader.get("ReturnID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.Groes = StringUtils.parseString(wReader.get("Groes"));
                wItem.Normt = StringUtils.parseString(wReader.get("Normt"));
                wItem.RFQTY = StringUtils.parseDouble(wReader.get("RFQTY"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.BatchNo = StringUtils.parseString(wReader.get("BatchNo"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.RowNo = StringUtils.parseString(wReader.get("RowNo"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));

                wItem.BackReason = StringUtils.parseString(wReader.get("BackReason"));
                wItem.BOMNumber = StringUtils.parseDouble(wReader.get("BOMNumber"));
                wItem.PLNumber = StringUtils.parseDouble(wReader.get("PLNumber"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.ReturnListID = StringUtils.parseInt(wReader.get("ReturnListID"));
                wItem.DemandItemID = StringUtils.parseInt(wReader.get("DemandItemID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.InspectorID = StringUtils.parseInt(wReader.get("InspectorID"));
                wItem.Inspector = StringUtils.parseString(wReader.get("Inspector"));
                wItem.BadNumber = StringUtils.parseDouble(wReader.get("BadNumber"));
                wItem.BadReason = StringUtils.parseString(wReader.get("BadReason"));
                wItem.GoodNumber = wItem.RFQTY - wItem.BadNumber;
                wItem.InspectTime = StringUtils.parseCalendar(wReader.get("InspectTime"));
                wItem.APSBOMItemNum = StringUtils.parseInt(wReader.get("APSBOMItemNum"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.ExpirationDate = StringUtils.parseString(wReader.get("ExpirationDate"));
                wItem.WarehousingDate = StringUtils.parseString(wReader.get("WarehousingDate"));
                wItem.SupplierLotNumber = StringUtils.parseString(wReader.get("SupplierLotNumber"));
                wItem.ItemStockID = StringUtils.parseInt(wReader.get("ItemStockID"));

                int wCYUnitID = StringUtils.parseInt(wReader.get("CYUnitID"));
                wItem.UnitText = APSConstans.GetCFGUnitName(wCYUnitID);

                wItem.ReplaceTypeText = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();

                // ①辅助属性
                wItem.ProductTimeText = wItem.ProductTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.ProductTime.getTime())
                        : "";
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.PartPointCode = APSConstans.GetFPCPartPointCode(wItem.PartPointID).replace("PS-", "");

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<Integer> SelectTaskIDList(BMSEmployee wLoginUser, String wMaterial, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT distinct ReturnID FROM {0}.mes_materialreturnitem where "
                    + "MaterialNo like ''%{1}%'' or MaterialName like ''%{1}%'';", wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wDemandID = StringUtils.parseInt(wReader.get("ReturnID"));
                if (wDemandID > 0 && wResult.stream().noneMatch(p -> p == wDemandID))
                    wResult.add(wDemandID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public void CloseReturnList(BMSEmployee wLoginUser, List<Integer> wReturnTaskIDList,
                                OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return;
            }

            String wSQL = StringUtils.Format("UPDATE {0}.aps_materialreturn SET STATUS=2,EditTime=now() WHERE ID IN" +
                            "({1});",
                    wInstance.Result, StringUtils.Join(",", wReturnTaskIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 关闭工艺变更退料消息
     */
    public void CloseTechMessage(BMSEmployee wLoginUser, int wOrderID, int wNoticeID, int partPointID,
                                 OutResult<Integer> wErrorCode) {
        try {
            //近一年的时间
            Calendar wStartTime = Calendar.getInstance();
            wStartTime.add(Calendar.YEAR, -1);
            Calendar wEndTime = Calendar.getInstance();
            wEndTime.add(Calendar.DATE, 1);

            //先查询
            List<BFCMessage> wList = CoreServiceImpl.getInstance().BFC_GetMessageListByCondition(wLoginUser, -1,
                    wOrderID, -1,
                    StringUtils.parseListArgs(BPMEventModule.TechReturnMaterial.getValue()), wNoticeID, partPointID,
                    null, -1, -1, wStartTime, wEndTime).List(BFCMessage.class);
            if(wList==null||wList.size()==0){
                return;
            }
            //再更新
            wList.forEach(p->p.Active=BFCMessageStatus.Close.getValue());
            CoreServiceImpl.getInstance().BFC_UpdateMessageList(wLoginUser, wList);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }
}
